package cn.com.yint.booklist.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cn.com.yint.booklist.model.WishList;
import cn.com.yint.booklist.dao.PageResult;

public class WishListDb {

	static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
	static final String DB_URL = "jdbc:mysql://localhost:3306/wishlist?useUnicode=true&character Encoding=UTF-8";
	static final String USER = "root";
	static final String PASS = "";
	private static Connection conn;
	private static WishList wishlist;

	public static Connection getConnection() {

		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
			//log("success");
			//System.out.println("\n");
			return  conn;
		} catch (SQLException se) {
			se.printStackTrace();
		} catch (Exception e) {
			// e.printStackTrace();
		}
        return null;
	}

	public static void clos() {
		try {
			conn.close();
		} catch (SQLException se) {
			se.printStackTrace();
		} catch (Exception e) {

		}
		try {
			if (conn != null)
				conn.close();
		} catch (SQLException se) {
			se.printStackTrace();
		}
	}

	public static List<WishList> getAll(int offset) {
		
		PreparedStatement stmt = null;
		Connection conn=getConnection();
		List<WishList> list = new ArrayList<WishList>();
		int size=10;
		offset=(offset-1)*10;

		try {
			String sql;
			
			sql = "SELECT ID,BOOKNAME,AUTHOR,PUBLISHER,AMOUNT,COMMENT,DATE FROM wl limit ?,?";
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, offset);
			stmt.setInt(2, size);
			ResultSet rs = stmt.executeQuery();
			while (rs.next()) {
				int id = rs.getInt("ID");
				//System.out.println(id);
				String bookname = rs.getString("BOOKNAME");
				String author = rs.getString("AUTHOR");
				String publisher = rs.getString("PUBLISHER");
				int amount = rs.getInt("AMOUNT");
				String comment = rs.getString("COMMENT");
				String date = rs.getString("DATE");

				WishList wishlist = new WishList();
				wishlist.setId(id);
				wishlist.setBookname(bookname);
				wishlist.setAuthor(author);
				wishlist.setPublisher(publisher);
				wishlist.setAmount(amount);
				wishlist.setComment(comment);
				wishlist.setDate(date);
				list.add(wishlist);
				
				}
				rs.close();
				stmt.close();
				clos();
			return list;
		} catch (SQLException se) {
			se.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		return null;

	}
public static List<WishList> getTen(int offset) {
		
		PreparedStatement stmt = null;
		Connection conn=getConnection();
		List<WishList> list = new ArrayList<WishList>();
		int size=10;
		offset=(offset-1)*10;

		try {
			String sql;
			
			sql = "SELECT ID,BOOKNAME,AUTHOR,PUBLISHER,AMOUNT,COMMENT,DATE FROM wl limit ?,?";
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, offset);
			stmt.setInt(2, size);
			ResultSet rs = stmt.executeQuery();
			while (rs.next()) {
				
				int id = rs.getInt("ID");
				//System.out.println(id);
				String bookname = rs.getString("BOOKNAME");
				String author = rs.getString("AUTHOR");
				String publisher = rs.getString("PUBLISHER");
				int amount = rs.getInt("AMOUNT");
				String comment = rs.getString("COMMENT");
				String date = rs.getString("DATE");

				WishList wishlist = new WishList();
				wishlist.setId(id);
				wishlist.setBookname(bookname);
				wishlist.setAuthor(author);
				wishlist.setPublisher(publisher);
				wishlist.setAmount(amount);
				wishlist.setComment(comment);
				wishlist.setDate(date);
				list.add(wishlist);
				
				}
				rs.close();
				stmt.close();
				clos();
			return list;
		} catch (SQLException se) {
			se.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		return null;

	}
	public static PageResult getPage() {
		
		PreparedStatement stmt = null;
		Connection conn=getConnection();
		PageResult pageresult=new PageResult();
		try {
			String sql;
			int rc=0;
			sql = "SELECT count(*) as recordcount FROM wl";
			stmt = conn.prepareStatement(sql);;
			ResultSet rs = stmt.executeQuery();
			while (rs.next()) {
				 rc = rs.getInt("recordcount");
				 pageresult.setCount(rc);
				 pageresult.setSize(10);
				// System.out.println(pageresult.getTotalPage());
				 //System.out.println(pageresult.getCount());
				}
				rs.close();
				stmt.close();
				clos();
			return pageresult;
		} catch (SQLException se) {
			se.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		return null;

	}


	public static boolean insert(WishList wishlist) {
		Connection conn=getConnection();
		PreparedStatement stmt = null;
		try {
			String sql2 = "INSERT INTO wl(BOOKNAME,AUTHOR,PUBLISHER,AMOUNT,COMMENT,DATE)VALUES(?,?,?,?,?,?)";
			stmt = conn.prepareStatement(sql2);
			stmt.setString(1, wishlist.getBookname());
			stmt.setString(2, wishlist.getAuthor());
			stmt.setString(3, wishlist.getPublisher());
			stmt.setInt(4, wishlist.getAmount());
			stmt.setString(5, wishlist.getComment());
			stmt.setString(6, wishlist.getDate());
			int result = stmt.executeUpdate();
			stmt.close();
			clos();
			return 1 == result;
		} catch (SQLException se) {
			se.printStackTrace();
		} catch (Exception e) {
			e.fillInStackTrace();
		}
		return false;
	}

	public static List<WishList> query(String bn) {
		PreparedStatement stmt = null;
		Connection conn=getConnection();
		List<WishList> list = new ArrayList<WishList>();
		try {
			String sql;
			sql = "SELECT ID,BOOKNAME,AUTHOR,PUBLISHER,AMOUNT,COMMENT,DATE FROM wl WHERE BOOKNAME=?";
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, bn);
			ResultSet rs = stmt.executeQuery();
			while (rs.next()) {
				int id = rs.getInt("ID");
				String bookname = rs.getString("BOOKNAME");
				String author = rs.getString("AUTHOR");
				String publisher = rs.getString("PUBLISHER");
				int amount = rs.getInt("AMOUNT");
				String comment = rs.getString("COMMENT");
				String date = rs.getString("DATE");
				
				WishList wishlist = new WishList();
				wishlist.setId(id);
				wishlist.setBookname(bookname);
				wishlist.setAuthor(author);
				wishlist.setPublisher(publisher);
				wishlist.setAmount(amount);
				wishlist.setComment(comment);
				wishlist.setDate(date);
				list.add(wishlist);
			}
			rs.close();
			stmt.close();
		} catch (SQLException se) {
			se.printStackTrace();
		} catch (Exception e) {

		}
		clos();
		return list;

	}

	public static boolean update(WishList wishlist) {
		PreparedStatement stmt = null;
		Connection conn=getConnection();
		try {
			String sql2 = "UPDATE wl SET BOOKNAME=?,AUTHOR=?,PUBLISHER=?,AMOUNT=?,COMMENT=?,DATE=? WHERE ID=?";
			stmt = conn.prepareStatement(sql2);
			stmt.setString(1, wishlist.getBookname());
			stmt.setString(2, wishlist.getAuthor());
			stmt.setString(3, wishlist.getPublisher());
			stmt.setInt(4, wishlist.getAmount());
			stmt.setString(5, wishlist.getComment());
			stmt.setString(6, wishlist.getDate());
			stmt.setInt(7, wishlist.getId());
			int result=stmt.executeUpdate();
			stmt.close();
			clos();
			return 1==result;
		} catch (SQLException se) {
			se.printStackTrace();
		} catch (Exception e) {

		}
        return false;
	}

	public static boolean delete(int id) {
		PreparedStatement stmt = null;
		Connection conn=getConnection();
		try {
			String sql2 = "DELETE FROM wl WHERE ID=?";
			stmt = conn.prepareStatement(sql2);
			stmt.setInt(1, id);
			int result=stmt.executeUpdate();
			stmt.close();
			clos();
			return 1==result;
		} catch (SQLException se) {
			se.printStackTrace();
		} catch (Exception e) {

		}
       return false;
	}
	public static WishList queryID(int id) {
		PreparedStatement stmt = null;
		Connection conn=getConnection();
		WishList wishlist = new WishList();
		try {
			String sql;
			sql = "SELECT ID,BOOKNAME,AUTHOR,PUBLISHER,AMOUNT,COMMENT,DATE FROM wl WHERE ID=?";
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, id);
			ResultSet rs = stmt.executeQuery();
			while (rs.next()) {
				int id1 = rs.getInt("ID");
				String bookname = rs.getString("BOOKNAME");
				String author = rs.getString("AUTHOR");
				String publisher = rs.getString("PUBLISHER");
				int amount = rs.getInt("AMOUNT");
				String comment = rs.getString("COMMENT");
				String date = rs.getString("DATE");
				
				wishlist.setId(id1);
				wishlist.setBookname(bookname);
				wishlist.setAuthor(author);
				wishlist.setPublisher(publisher);
				wishlist.setAmount(amount);
				wishlist.setComment(comment);
				wishlist.setDate(date);
				
			}
			rs.close();
			stmt.close();
		} catch (SQLException se) {
			se.printStackTrace();
		} catch (Exception e) {

		}
		clos();
		return wishlist;

	}

	public static WishList getWishlist() {
		return wishlist;
	}

	public static void setWishlist(WishList wishlist) {
		WishListDb.wishlist = wishlist;
	}
}

